package com.imesne.office.excel.utils;

import com.imesne.office.excel.exception.OfficeException;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.format.CellFormat;
import org.apache.poi.ss.format.CellFormatResult;
import org.apache.poi.ss.usermodel.*;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * Created by liyd on 17/6/29.
 */
public class ExcelKitUtils {

    public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
    public static final int UNIT_OFFSET_LENGTH = 7;
    public static final int[] UNIT_OFFSET_MAP = new int[]{0, 36, 73, 109, 146, 182, 219};


    /**
     * 用于将Excel表格中列号字母转成列索引，从1对应A开始
     *
     * @param column 列号
     * @return 列索引
     */
    public static int columnToNum(String column) {
        int num = 0;
        char[] chars = column.toUpperCase().toCharArray();
        for (int i = 0; i < chars.length; i++) {
            num += ((int) chars[i] - (int) 'A' + 1)
                    * (int) Math.pow(26, chars.length - i - 1);
        }
        return num;
    }

    /**
     * 将列号转换成数字值，例如 F8 -> {6,8}
     *
     * @param cellRef
     * @return
     */
    public static int[] cellRefToNum(String cellRef) {

        String cellNum = null;
        String rowNum = null;
        for (int i = 0; i < cellRef.length(); i++) {
            char c = cellRef.charAt(i);
            if (Character.isDigit(c)) {
                cellNum = StringUtils.substring(cellRef, 0, i);
                rowNum = StringUtils.substring(cellRef, i);
                break;
            }
        }
        int index = columnToNum(cellNum);
        return new int[]{index, Integer.valueOf(rowNum)};
    }

    /**
     * 用于将excel表格中列索引转成列号字母，从A对应1开始
     *
     * @param num 列索引
     * @return 列号
     */
    public static String numToColumn(int num) {
        num--;
        String column = "";
        do {
            if (column.length() > 0) {
                num--;
            }
            column = ((char) (num % 26 + (int) 'A')) + column;
            num = (int) ((num - num % 26) / 26);
        } while (num > 0);
        return column;
    }

    public static Workbook createWorkbook(File file) {
        try {
            Workbook workbook = WorkbookFactory.create(file);
            return workbook;
        } catch (Exception e) {
            throw new OfficeException("读取excel文件失败", e);
        }
    }

    public static Workbook createWorkbook(byte[] bytes) {
        try {
            Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bytes));
            return workbook;
        } catch (Exception e) {
            throw new OfficeException("读取excel文件失败", e);
        }
    }

    /**
     * 将文件转换成byte数组
     *
     * @param file
     * @return
     */
    public static byte[] readFileToByte(File file) {
        try {
            byte[] bytes = FileUtils.readFileToByteArray(file);
            return bytes;
        } catch (IOException e) {
            throw new OfficeException("将文件转换成byte数组失败", e);
        }

    }

    /**
     * pixel units to excel width units(units of 1/256th of a character width)
     *
     * @param pxs
     * @return
     */
    public static short pixel2WidthUnits(int pxs) {
        short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
        widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
        return widthUnits;
    }

    /**
     * excel width units(units of 1/256th of a character width) to pixel units
     *
     * @param widthUnits
     * @return
     */
    public static int widthUnits2Pixel(int widthUnits) {
        int pixels = (widthUnits / EXCEL_COLUMN_WIDTH_FACTOR) * UNIT_OFFSET_LENGTH;
        int offsetWidthUnits = widthUnits % EXCEL_COLUMN_WIDTH_FACTOR;
        pixels += Math.round(offsetWidthUnits
                / ((float) EXCEL_COLUMN_WIDTH_FACTOR / UNIT_OFFSET_LENGTH));

        return pixels;
    }

    /**
     * 获取cell的值
     *
     * @param cell
     * @return
     */
    public static String getCellValue(Cell cell) {
        String value = "";
        if (cell == null) {
            return value;
        }
        CellStyle cellStyle = cell.getCellStyle();
        String dataFormatString = cellStyle.getDataFormatString();
        if (dataFormatString == null) {
            CellType cellType = cell.getCellTypeEnum();
            if (cellType == CellType.FORMULA) {
                value = cell.getCellFormula();
            } else if (cellType == CellType.BOOLEAN) {
                value = String.valueOf(cell.getBooleanCellValue());
            } else if (cellType == CellType.NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = StringUtils.replace(dateFormat.format(date), " 00:00:00", "");
                } else {
                    double numericCellValue = cell.getNumericCellValue();
                    String tmp = String.valueOf(numericCellValue);
                    if (StringUtils.length(tmp) > 10 && StringUtils.indexOf(tmp, ".") == -1) {
                        DecimalFormat df = new DecimalFormat("0");
                        value = df.format(numericCellValue);
                    } else {
                        value = tmp;
                    }
                }
            } else {
                value = cell.getStringCellValue();
            }
        } else {
            CellFormat cf = CellFormat.getInstance(dataFormatString);
            CellFormatResult result = cf.apply(cell);
            value = result.text;
        }
        return value;
    }

}
